Regular Expressions (REGEXP) in MySQL

Basic Syntax of REGEXP in MySQL

Regular expressions in MySQL are powerful tools used to match text patterns using rules. They are especially useful in WHERE clauses to filter data based on specific character combinations.

SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern';

What is a Regular Expression?

A Regular Expression (REGEXP) is a sequence of characters that defines a search pattern. In MySQL, it works similar to patterns in other programming languages, but not all features are the same.

Common REGEXP Operators in MySQL

Pattern Meaning Example
^ Start of string ^abc matches any string starting with "abc"
$ End of string abc$ matches any string ending with "abc"
. Any single character a.c matches "abc", "acc", etc.
[...] Any character in the set [aeiou] matches any vowel
[^...] Any character not in the set [^0-9] matches any non-digit
| OR operator abc|xyz matches "abc" or "xyz"
* 0 or more repetitions of the previous character a* matches "", "a", "aa", etc.
+ 1 or more repetitions a+ matches "a", "aa", "aaa"
{n} Exactly n repetitions a{3} matches "aaa"
{n,} n or more repetitions a{2,} matches "aa", "aaa", etc.
{n,m} Between n and m repetitions a{2,4} matches "aa", "aaa", "aaaa"
[a-z] Any lowercase letter b[a-z]g matches "bag", "bog"

Case Sensitivity

  • By default, REGEXP is case-insensitive in MySQL 8.0+
  • You can use BINARY to make it case-sensitive.
SELECT * FROM users WHERE BINARY name REGEXP '^A';

Examples with Table

Suppose you have a table:

CREATE TABLE Users ( id INT, name VARCHAR(50), email VARCHAR(100) ); INSERT INTO Users VALUES (1, 'Alice', 'alice@gmail.com'), (2, 'Bob', 'bob@yahoo.com'), (3, 'Charlie', 'charlie@leetcode.com'), (4, 'Ankit', 'ankit@leetcode.COM'), (5, 'Angela', 'angela@outlook.com');

Example 1: Starts with A

SELECT * FROM Users WHERE name REGEXP '^A';

Matches: Alice, Ankit, Angela

Example 2: Email ends with @leetcode.com (case-insensitive)

SELECT * FROM Users WHERE email REGEXP '@leetcode\\.com$';

Matches: Only charlie@leetcode.com

(Use BINARY if you want exact case match.)

Example 3: Emails from Gmail or Yahoo

SELECT * FROM Users WHERE email REGEXP 'gmail|yahoo';

Matches: alice@gmail.com, bob@yahoo.com

Example 4: Names with only letters (no digits or special characters)

SELECT * FROM Users WHERE name REGEXP '^[A-Za-z]+$';

Example 5: Name contains at least one digit

SELECT * FROM Users WHERE name REGEXP '[0-9]';

Special Character Escaping

Some characters like ., *, ?, +, ^, $, |, (, ), {, }, [, ], and \ have special meanings and must be escaped with double backslashes (\\) in SQL strings.

Example:

SELECT * FROM Users WHERE email REGEXP '\\.com$';

Using NOT REGEXP

You can also exclude results using NOT REGEXP

SELECT * FROM Users WHERE name NOT REGEXP '^A';

REGEXP vs LIKE

Feature REGEXP LIKE
Pattern Matching Complex (supports ranges, OR, etc) Simple
Wildcards [a-z], *, +, |, etc. % and _ only
Power High Low

Full Example Query

SELECT name, email FROM Users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@leetcode\\.com$';

✅ This matches only properly formatted emails ending in @leetcode.com.

MySQL 8.0+: REGEXP_LIKE() (Alternative Function)

In MySQL 8.0 and above, you can also use:

SELECT * FROM Users WHERE REGEXP_LIKE(email, '@gmail\\.com$');

This function behaves the same but is more ANSI-compliant and easier to read.

Tips

Always escape dots (.) when you want to match a literal dot in email/domain.
Use BINARY if case-sensitivity is required.
Prefer REGEXP_LIKE() over REGEXP in MySQL 8+ for clarity.

Would you like a practice sheet with questions and solutions based on REGEXP?